﻿Imports System.Data
Imports System.Data.SqlClient
Public Class knm2012DAO
    Private connect As Connect
    Dim sqlstr As String
    Public Sub New()
        connect = New Connect
    End Sub

#Region "tabcongtruong"
    Public Function GetDataCT() As List(Of congtruong)
        Dim i As Integer
        Dim macongtruong, tencongtruong, caodiem, tang, khuvuc As String
        Dim congtruong As congtruong
        Dim n, e As Double
        Dim listcongtruong As New List(Of congtruong)
        Dim dt As New DataTable
        dt = connect.GetData("select* from CongTruong")
        For i = 0 To dt.Rows.Count() - 1
            macongtruong = dt.Rows(i)("MaCongTruong").ToString
            tencongtruong = dt.Rows(i)("TenCongTruong").ToString
            caodiem = dt.Rows(i)("CaoDiem").ToString
            tang = dt.Rows(i)("Tang").ToString
            khuvuc = dt.Rows(i)("KhuVuc").ToString
            n = dt.Rows(i)("N")
            e = dt.Rows(i)("E")
            congtruong = New congtruong(macongtruong, tencongtruong, caodiem, tang, khuvuc, n, e)
            listcongtruong.Add(congtruong)
        Next
        Return listcongtruong
    End Function
    Public Function InsertUpdateCT(ByVal congtruong As congtruong, ByVal flag As Integer) As Boolean
        Try
            Dim macongtruong As String = congtruong.Macongtruong
            Dim tencongtruong As String = congtruong.Tencongtruong
            Dim caodiem As String = congtruong.Caodiem
            Dim tang As String = congtruong.Tang
            Dim khuvuc As String = congtruong.Khuvuc
            Dim n As Double = congtruong.N
            Dim e As Double = congtruong.E
            If (flag = 1) Then
                sqlstr = "INSERT CongTruong (MaCongTruong, TenCongTruong, CaoDiem, Tang, KhuVuc,N,E) VALUES(N'" & macongtruong & "',N'" & tencongtruong & "',N'" & caodiem & "', N'" & tang & "', N'" & khuvuc & "','" & n & "','" & e & "')"
            End If
            If (flag = 2) Then
                sqlstr = "UPDATE CongTruong SET TenCongTruong= N'" & tencongtruong & "', CaoDiem=N'" & caodiem & "', Tang=N'" & tang & "', KhuVuc= N'" & khuvuc & "',N='" & n & "',E='" & e & "' WHERE MaCongTruong='" & macongtruong & "'"
            End If
            Return connect.Excute(sqlstr)
        Catch ex As Exception
            MsgBox("Dữ liệu công trường đã tồn tại, vui lòng kiểm tra lại!", MsgBoxStyle.Exclamation, "Thông báo")
        End Try
        Return True
    End Function

    Public Function DeleteCT(ByVal congtruong As congtruong)
        Dim macongtruong As String = congtruong.Macongtruong
        sqlstr = "DELETE CongTruong WHERE MaCongTruong='" & macongtruong & "'"
        Return connect.Excute(sqlstr)
    End Function

#End Region

#Region "tabdatda"
    Public Function GetDaTaDD() As List(Of datda)
        Dim i As Integer
        Dim madatda, tendatda As String
        Dim docung As Single
        Dim datda As datda
        Dim listdatda As New List(Of datda)
        Dim dt As New DataTable
        dt = connect.GetData("select* from DatDa")
        For i = 0 To dt.Rows.Count() - 1
            madatda = dt.Rows(i)("MaDatDa").ToString
            tendatda = dt.Rows(i)("TenDatDa").ToString
            docung = dt.Rows(i)("DoCung").ToString
            datda = New datda(madatda, tendatda, docung)
            listdatda.Add(datda)
        Next
        Return listdatda
    End Function
    Public Function DeleteDD(ByVal datda As datda)
        Dim madatda As String = datda.Madatda
        sqlstr = "DELETE DatDa WHERE MaDatDa='" & madatda & "'"
        Return connect.Excute(sqlstr)
    End Function
    Public Function InsertUpdateDD(ByVal datda As datda, ByVal flag As Integer)
        Dim tb As Boolean
        Try
            Dim madatda As String = datda.Madatda
            Dim tendatda As String = datda.Tendatda
            Dim docung As Single = datda.Docung
            If (flag = 1) Then
                sqlstr = "INSERT DatDa (MaDatDa, TenDatDa, DoCung) VALUES(N'" & madatda & "',N'" & tendatda & "','" & docung & "')"
                MsgBox("Thêm mới dữ liệu thành công", MsgBoxStyle.Information, "Thông báo")
            End If
            If (flag = 2) Then
                sqlstr = "UPDATE DatDa SET TenDatDa= N'" & tendatda & "', DoCung='" & docung & "' WHERE MaDatDa='" & madatda & "'"
                MsgBox("Cập nhật dữ liệu thành công", MsgBoxStyle.Information, "Thông báo")
            End If
            Return connect.Excute(sqlstr)
            tb = True
        Catch ex As Exception
            MsgBox("Dữ liệu đất đá đã tồn tại, vui lòng kiểm tra lại!", MsgBoxStyle.Exclamation, "Thông báo")
        End Try
        If tb = True Then
            MsgBox("Cập nhật dữ liệu thành công!", MsgBoxStyle.Information, "Thông báo")
        End If
        Return True
    End Function
#End Region

#Region "tabmaykhoan"
    Public Function GetDataMK() As List(Of maykhoan)
        Dim i As Integer
        Dim mamaykhoan, tenmaykhoan As String
        Dim dkmuikhoan, chieusaukhoantd, huongkhoan As Single
        Dim maykhoan As maykhoan
        Dim listmaykhoan As New List(Of maykhoan)
        Dim dt As New DataTable
        dt = connect.GetData("Select * from MayKhoan")
        For i = 0 To dt.Rows.Count() - 1
            mamaykhoan = dt.Rows(i)("MaMayKhoan").ToString
            tenmaykhoan = dt.Rows(i)("TenMayKhoan").ToString
            dkmuikhoan = dt.Rows(i)("DkMuiKhoan").ToString
            chieusaukhoantd = dt.Rows(i)("ChieuSauKhoan").ToString
            huongkhoan = dt.Rows(i)("HuongKhoan").ToString
            maykhoan = New maykhoan(mamaykhoan, tenmaykhoan, dkmuikhoan, chieusaukhoantd, huongkhoan)
            listmaykhoan.Add(maykhoan)
        Next
        Return listmaykhoan
    End Function
    Public Function DeleteMK(ByVal maykhoan As maykhoan)
        Dim mamaykhoan As String = maykhoan.Mamaykhoan
        sqlstr = "DELETE MayKhoan WHERE MaMayKhoan='" & mamaykhoan & "'"
        Return connect.Excute(sqlstr)
    End Function
    Public Function InsertUpdateMK(ByVal maykhoan As maykhoan, flag As Integer)
        Try
            Dim mamaykhoan As String = maykhoan.Mamaykhoan
            Dim tenmaykhoan As String = maykhoan.Tenmaykhoan
            Dim dkmuikhoan As Single = maykhoan.Dkmuikhoan
            Dim chieusaukhoantd As Single = maykhoan.Chieusaukhoantd
            Dim huongkhoan As String = maykhoan.Huongkhoan
            If (flag = 1) Then
                sqlstr = "INSERT MayKhoan (MaMayKhoan,TenMayKhoan,DkMuiKhoan,ChieuSauKhoan,HuongKhoan) VALUES (N'" & mamaykhoan & "',N'" & tenmaykhoan & "',N'" & dkmuikhoan & "', N'" & chieusaukhoantd & "', N'" & huongkhoan & "')"
                MsgBox("Thêm mới dữ liệu thành công", MsgBoxStyle.Information, "Thông báo")
            End If
            If (flag = 2) Then
                sqlstr = "UPDATE MayKhoan SET TenMayKhoan= N'" & tenmaykhoan & "', DkMuiKhoan=N'" & dkmuikhoan & "',ChieuSauKhoan=N'" & chieusaukhoantd & "',HuongKhoan=N'" & huongkhoan & "' WHERE MaMayKhoan=N'" & mamaykhoan & "'"
                MsgBox("Cập nhật dữ liệu thành công", MsgBoxStyle.Information, "Thông báo")
            End If
            Return connect.Excute(sqlstr)
        Catch ex As Exception
            MsgBox("Dữ liệu máy khoan đã tồn tại, vui lòng kiểm tra lại", MsgBoxStyle.Exclamation, "Thông báo")
        End Try
        Return True
    End Function
#End Region

#Region "tablokhoan"
    Public Function GetDataLK() As List(Of lokhoan)
        Dim i As Integer
        Dim malokhoan, macongtruong, ghichu As String
        Dim chieucaotang, duongkinhlokhoan, chieusaukhoanthem, chieusaulokhoan, chieucaocotnuoc, gocnghienglokhoan As Single
        Dim lokhoan As lokhoan
        Dim listlokhoan As New List(Of lokhoan)
        Dim dt As New DataTable
        dt = connect.GetData("Select * from LoKhoan")
        For i = 0 To dt.Rows.Count() - 1
            malokhoan = dt.Rows(i)("MaLoKhoan").ToString
            macongtruong = dt.Rows(i)("MaCongTruong").ToString
            ghichu = dt.Rows(i)("GhiChu").ToString
            chieucaotang = dt.Rows(i)("ChieuCaoTang").ToString
            duongkinhlokhoan = dt.Rows(i)("DKLoKhoan").ToString
            chieusaukhoanthem = dt.Rows(i)("ChieuSaukt").ToString
            chieusaulokhoan = dt.Rows(i)("ChieuSau").ToString
            chieucaocotnuoc = dt.Rows(i)("ChieuCaoCotNuoc").ToString
            gocnghienglokhoan = dt.Rows(i)("GocNghiengLK").ToString
            lokhoan = New lokhoan(malokhoan, macongtruong, ghichu, chieucaotang, duongkinhlokhoan, chieusaukhoanthem, chieusaulokhoan, chieucaocotnuoc, gocnghienglokhoan)
            listlokhoan.Add(lokhoan)
        Next
        Return listlokhoan
    End Function
    Public Function DeleteLK(ByVal lokhoan As lokhoan)
        Dim malk As String = lokhoan.Malokhoan
        sqlstr = "DELETE LoKhoan WHERE MaLoKhoan='" & malk & "'"
        Return connect.Excute(sqlstr)
    End Function
    Public Function InsertUpdateLK(ByVal lokhoan As lokhoan, ByVal flag As Integer) As Boolean
        Try
            Dim malk As String = lokhoan.Malokhoan
            Dim mact As String = lokhoan.MaCongTruong
            Dim ghichu As String = lokhoan.Ghichu
            Dim chieucaotang As Single = lokhoan.Chieucaotang
            Dim dklokhoan As Single = lokhoan.Duongkinhlokhoan
            Dim chieusaukt As Single = lokhoan.Chieusaukhoanthem
            Dim chieusau As Single = lokhoan.Chieusaulokhoan
            Dim chieucaocotnuoc As Single = lokhoan.Chieucaocotnuoc
            Dim gocnghieng As Single = lokhoan.Gocnghieng
            If flag = 1 Then
                sqlstr = "INSERT LoKhoan(MaLoKhoan,GhiChu,MaCongTruong, ChieuCaoTang,DKLoKhoan,ChieuSaukt,ChieuSau,ChieuCaoCotNuoc,GocNghiengLK) VALUES(N'" & malk & "',N'" & ghichu & "',N'" & mact & "',N'" & chieucaotang & "',N'" & dklokhoan & "', N'" & chieusaukt & "',N'" & chieusau & "',N'" & chieucaocotnuoc & "',N'" & gocnghieng & "')"
                MsgBox("Thêm mới dữ liệu thành công", MsgBoxStyle.Information, "Thông báo")
            End If
            If flag = 2 Then
                sqlstr = "UPDATE LoKhoan SET GhiChu='" & ghichu & "',MaCongTruong=N'" & mact & "',ChieuCaoTang='" & chieucaotang & "',DKLoKhoan='" & dklokhoan & "',ChieuSaukt='" & chieusaukt & "',ChieuSau='" & chieusau & "',ChieuCaoCotNuoc='" & chieucaocotnuoc & "',GocNghiengLK='" & gocnghieng & "' WHERE MaLoKhoan='" & malk & "'"
                MsgBox("Cập nhật dữ liệu thành công", MsgBoxStyle.Information, "Thông báo")
            End If
            Return connect.Excute(sqlstr)
        Catch ex As Exception
            MsgBox("Dữ liệu lỗ khoan đã tồn tại, vui lòng kiểm tra lại", MsgBoxStyle.Exclamation, "Thông báo")
        End Try
        Return True
    End Function
#End Region

#Region "tabthuocno"
    Public Function GetDataTN() As List(Of thuocno)
        Dim i As Integer
        Dim mathuocno, tenthuocno, tocdono, khanangsinhcong, khanangchiunuoc, donvitinh As String
        Dim hesochuyendoi, giathanh As Single
        Dim thuocno As thuocno
        Dim listthuocno As New List(Of thuocno)
        Dim dt As New DataTable
        dt = connect.GetData("Select * from ThuocNo")
        For i = 0 To dt.Rows.Count() - 1
            mathuocno = dt.Rows(i)("MaThuocNo")
            tenthuocno = dt.Rows(i)("TenThuocNo")
            tocdono = dt.Rows(i)("TocDoNo")
            khanangsinhcong = dt.Rows(i)("KhaNangSinhCong")
            khanangchiunuoc = dt.Rows(i)("KhaNangChiuNuoc")
            hesochuyendoi = dt.Rows(i)("HSChuyenDoi")
            donvitinh = dt.Rows(i)("DonViTinh")
            giathanh = dt.Rows(i)("GiaThanh")
            thuocno = New thuocno(mathuocno, tenthuocno, tocdono, khanangsinhcong, khanangchiunuoc, hesochuyendoi, donvitinh, giathanh)
            listthuocno.Add(thuocno)
        Next
        Return listthuocno
    End Function
    Public Function DeleteTN(ByVal thuocno As thuocno)
        Dim mathuocno As String = thuocno.Mathuocno
        sqlstr = "Delete ThuocNo Where MaThuocNo='" & mathuocno & "'"
        Return connect.Excute(sqlstr)
    End Function
    Public Function InsertUpdateTN(ByVal thuocno As thuocno, flag As Integer) As Boolean
        Try
            Dim mathuocno As String = thuocno.Mathuocno
            Dim tenthuocno As String = thuocno.Tenthuocno
            Dim tocdono As String = thuocno.Tocdono
            Dim khanangsinhcong As String = thuocno.Khanangsinhcong
            Dim khanangchiunuoc As String = thuocno.Khanangchiunuoc
            Dim hesochuyendoi As String = thuocno.Hesochuyendoi
            Dim donvitinh As String = thuocno.Donvitinh
            Dim giathanh As String = thuocno.Giathanh
            If flag = 1 Then
                sqlstr = "INSERT ThuocNo(MaThuocNo, TenThuocNo, TocDoNo, KhaNangSinhCong, KhaNangChiuNuoc, HSChuyenDoi, DonViTinh, GiaThanh) VALUES (N'" & mathuocno & "', N'" & tenthuocno & "', N'" & tocdono & "', N'" & khanangsinhcong & "', N'" & khanangchiunuoc & "', N'" & hesochuyendoi & "', N'" & donvitinh & "', N'" & giathanh & "') "
                MsgBox("Thêm mới dữ liệu thành công", MsgBoxStyle.Information, "Thông báo")
            End If
            If flag = 2 Then
                sqlstr = "UPDATE ThuocNo SET TenThuocNo=N'" & tenthuocno & "', TocDoNo='" & tocdono & "', KhaNangSinhCong='" & khanangsinhcong & "', KhaNangChiuNuoc='" & khanangchiunuoc & "',HSChuyenDoi='" & hesochuyendoi & "', DonViTinh='" & donvitinh & "', GiaThanh='" & giathanh & "' WHERE MaThuocNo='" & mathuocno & "'"
                MsgBox("Cập nhật dữ liệu thành công", MsgBoxStyle.Information, "Thông báo")
            End If
            Return connect.Excute(sqlstr)
            'MsgBox("Cập nhật dữ liệu thành công", MsgBoxStyle.Information, "Thông báo")
        Catch ex As Exception
            MsgBox("Dữ liệu thuốc nổ đã tồn tại, vui lòng kiểm tra lại", MsgBoxStyle.Exclamation, "Thông báo")
        End Try
        Return True
    End Function
#End Region

#Region "tabkipno"
    Public Function GetDataKN() As List(Of kipno)
        Dim i As Integer
        Dim makip, tenkip, donvitinh As String
        Dim thoigianvisai, duongkinhkip, chieudaikip, giathanh As Single
        Dim kipno As kipno
        Dim listkipno As New List(Of kipno)
        Dim dt As New DataTable
        dt = connect.GetData("Select * from KipNo")
        For i = 0 To dt.Rows.Count() - 1
            makip = dt.Rows(i)("MaKipNo")
            tenkip = dt.Rows(i)("TenKipNo")
            thoigianvisai = dt.Rows(i)("TGViSai")
            duongkinhkip = dt.Rows(i)("DKip")
            chieudaikip = dt.Rows(i)("LKip")
            donvitinh = dt.Rows(i)("DonViTinh")
            giathanh = dt.Rows(i)("GiaThanh")
            kipno = New kipno(makip, tenkip, thoigianvisai, duongkinhkip, chieudaikip, donvitinh, giathanh)
            listkipno.Add(kipno)
        Next
        Return listkipno
    End Function
    Public Function DeleteKN(ByVal kipno As kipno)
        Dim makn As String = kipno.Makipno
        sqlstr = "DELETE KipNo WHERE MaKipNo='" & makn & "'"
        Return connect.Excute(sqlstr)
    End Function
    Public Function InsertUpdateKN(ByVal kipno As kipno, ByVal flag As Integer) As Boolean
        Try
            Dim makip As String = kipno.Makipno
            Dim tenkip As String = kipno.Tenkipno
            Dim thoigianvisai As String = kipno.Thoigianvisai
            Dim duongkinhkip As Single = kipno.Duongkinhkip
            Dim chieudaikip As Single = kipno.Chieudaikip
            Dim donvitinh As String = kipno.Donvitinh
            Dim giathanh As String = kipno.Giathanh
            If flag = 1 Then
                sqlstr = "INSERT ThuocNo(MaKipNo,TenKipNo,TGViSai,DKip,LKip,DonViTinh,GiaThanh) VALUES(N'" & makip & "',N'" & tenkip & "', N'" & thoigianvisai & "', N'" & duongkinhkip & "',N'" & chieudaikip & "', N'" & donvitinh & "', N'" & giathanh & "')"
                MsgBox("Thêm mới dữ liệu thành công!", MsgBoxStyle.Information, "Thông báo")
            End If
            If flag = 2 Then
                sqlstr = "UPDATE KipNo Set TenKipNo=N'" & tenkip & "', TGViSai='" & thoigianvisai & "', DKip='" & duongkinhkip & "', LKip='" & chieudaikip & "', DonViTinh=N'" & donvitinh & "', GiaThanh='" & giathanh & "' WHERE MaKipNo='" & makip & "'"
                MsgBox("Cập nhật dữ liệu thành công!", MsgBoxStyle.Information, "Thông báo")
            End If
            Return connect.Excute(sqlstr)
        Catch ex As Exception
            MsgBox("Dữ liệu thuốc nổ đã tồn tại, vui lòng kiểm tra lại", MsgBoxStyle.Exclamation, "Thông báo")
        End Try
        Return True
    End Function
#End Region

#Region "tabdayno"
    Public Function GetDaTaDN() As List(Of dayno)
        Dim i As Integer
        Dim madayno, tendayno, duongkinhngoai, khanangchiunuoc, khanangchiunhiet, donvitinh, tocdono As String
        Dim matdothuoc, giathanh As Single
        Dim dayno As dayno
        Dim daynoList As New List(Of dayno)
        Dim dt As New DataTable
        dt = connect.GetData("SELECT * FROM DayNo")
        For i = 0 To dt.Rows.Count - 1
            madayno = dt.Rows(i)("MaDayNo")
            tendayno = dt.Rows(i)("TenDayNo")
            duongkinhngoai = dt.Rows(i)("DNgoai")
            matdothuoc = dt.Rows(i)("MatDoThuoc")
            tocdono = dt.Rows(i)("VNo")
            khanangchiunuoc = dt.Rows(i)("KNChiuNuoc")
            khanangchiunhiet = dt.Rows(i)("KNChiuNhiet")
            donvitinh = dt.Rows(i)("DVtinh")
            giathanh = dt.Rows(i)("Giathanh")
            dayno = New dayno(madayno, tendayno, duongkinhngoai, matdothuoc, tocdono, khanangchiunuoc, khanangchiunhiet, donvitinh, giathanh)
            daynoList.Add(dayno)
        Next
        Return daynoList
    End Function
    Public Function DeleteDN(ByVal dayno As dayno)
        Dim madayno = dayno.Madayno
        sqlstr = "DELETE DayNo WHERE MaDayNo='" & madayno & "'"
        Return connect.Excute(sqlstr)
    End Function
    Public Function InsertUpdateDN(ByVal dayno As dayno, ByVal flag As Integer) As Boolean
        Try
            Dim madayno As String = dayno.Madayno
            Dim tendayno As String = dayno.Tendayno
            Dim dkngoai As String = dayno.Duongkinhngoai
            Dim matdothuoc As Single = dayno.Duongkinhngoai
            Dim tocdono As Single = dayno.Matdothuoc
            Dim knchiunhiet As String = dayno.Khanangchiunhiet
            Dim knchiunuoc As String = dayno.Khanangchiunuoc
            Dim dvtinh As String = dayno.Donvitinh
            Dim giathanh As Single = dayno.Giathanh
            If (flag = 1) Then
                sqlstr = "INSERT DayNo(MaDayNo, TenDayNo, DNgoai, MatDoThuoc, VNo,KNChiuNuoc,KNChiuNhiet,DVTinh,GiaThanh) VALUES (N'" & madayno & "','" & tendayno & "', N'" & dkngoai & "' , N'" & matdothuoc & "', N'" & tocdono & "', N'" & knchiunuoc & "', N'" & knchiunhiet & "', N'" & dvtinh & "', N'" & giathanh & "')"
            End If
            If (flag = 2) Then
                sqlstr = "UPDATE DayNo SET TenDayno=N'" & tendayno & "',DNgoai=N'" & dkngoai & "', MatDoThuoc=N'" & matdothuoc & "', VNo=N'" & tocdono & "', KNChiuNuoc=N'" & knchiunuoc & "', KNChiuNhiet=N'" & knchiunhiet & "', DVTinh=N'" & dvtinh & "',GiaThanh=N'" & giathanh & "' WHERE MaDayNo='" & madayno & "'"
            End If
            Return connect.Excute(sqlstr)
        Catch ex As Exception
            MsgBox("Dữ liệu dây nổ đã tồn tại, vui lòng kiểm tra lại", MsgBoxStyle.Exclamation, "Thông báo")
        End Try
        Return True
    End Function
#End Region

#Region "tabmaynomin"
    Public Function GetDataMN() As List(Of maynomin)
        Dim i As Integer
        Dim mayno As maynomin
        Dim mamayno, tenmayno As String
        Dim maynoList As New List(Of maynomin)
        Dim dt As New DataTable
        dt = connect.GetData("Select* from MayNoMin")
        For i = 0 To dt.Rows.Count - 1
            mamayno = dt.Rows(i)("MaMayNoMin")
            tenmayno = dt.Rows(i)("TenMayNoMin")
            mayno = New maynomin(mamayno, tenmayno)
            maynoList.Add(mayno)
        Next
        Return maynoList
    End Function
    Public Function DeleteMN(ByVal mayno As maynomin)
        Dim mamayno = mayno.Mamayno
        sqlstr = "Delete MayNo Where MaMayNoMin='" & mamayno & "'"
        Return connect.Excute(sqlstr)
    End Function
    Public Function InsertUpdateMN(ByVal mayno As maynomin, flag As Integer) As Boolean
        Try
            Dim mamayno As String = mayno.Mamayno
            Dim tenmayno As String = mayno.Tenmayno
            If (flag = 1) Then
                sqlstr = "INSERT MayNoMin(MaMayNoMin, TenMayNoMin) VALUES(N'" & mamayno & "', N'" & tenmayno & "')"
            End If
            If (flag = 2) Then
                sqlstr = "UPDATE MayNoMin SET TenMayNoMin=N'" & tenmayno & "' WHERE MaMayNoMin=N'" & mamayno & "'"
            End If
            Return connect.Excute(sqlstr)
        Catch ex As Exception
            MsgBox("Dữ liệu máy nổ mìn đã tồn tại, vui lòng kiểm tra lại", MsgBoxStyle.Exclamation, "Thông báo")
        End Try
        Return True
    End Function
#End Region

#Region "tabphuongphapno"
    Public Function GetDataPPN() As List(Of phuongphapno)
        Dim i As Integer
        Dim ppno As phuongphapno
        Dim mappn, tenppn As String
        Dim ppnList As New List(Of phuongphapno)
        Dim dt As New DataTable
        dt = connect.GetData("Select* from PPNo")
        For i = 0 To dt.Rows.Count - 1
            mappn = dt.Rows(i)("MaPPNo")
            tenppn = dt.Rows(i)("TenPPNo")
            ppno = New phuongphapno(mappn, tenppn)
            ppnList.Add(ppno)
        Next
        Return ppnList
    End Function
    Public Function DeletePPN(ByVal ppno As phuongphapno)
        Dim mappn = ppno.Mappn
        sqlstr = "Delete PPNo Where MaPPNo='" & mappn & "'"
        Return connect.Excute(sqlstr)
    End Function
    Public Function InsertUpdatePPN(ByVal ppno As phuongphapno, flag As Integer) As Boolean
        Try
            Dim mappn As String = ppno.Mappn
            Dim tenppn As String = ppno.Tenppn
            If (flag = 1) Then
                sqlstr = "INSERT PPNo(MaPPNo, TenPPNo) VALUES(N'" & mappn & "', N'" & tenppn & "')"
            End If
            If (flag = 2) Then
                sqlstr = "UPDATE PPNo SET TenPPNo=N'" & tenppn & "' WHERE MaPPNo=N'" & mappn & "'"
            End If
            Return connect.Excute(sqlstr)
        Catch ex As Exception
            MsgBox("Dữ liệu máy nổ mìn đã tồn tại, vui lòng kiểm tra lại", MsgBoxStyle.Exclamation, "Thông báo")

        End Try
        Return True
    End Function
#End Region

#Region "tabphukienno"
    Public Function GetDataPK() As List(Of phukienno)
        Dim i As Integer
        Dim pkno As phukienno
        Dim mapk, tenpk, donvitinh As String
        Dim giathanh As Integer
        Dim pkList As New List(Of phukienno)
        Dim dt As New DataTable
        dt = connect.GetData("select * from PKNo")
        For i = 0 To dt.Rows.Count - 1
            mapk = dt.Rows(i)("MaPKNo")
            tenpk = dt.Rows(i)("TenPKNo")
            giathanh = dt.Rows(i)("GiaThanh")
            donvitinh = dt.Rows(i)("DonViTinh")
            pkno = New phukienno(mapk, tenpk, giathanh, donvitinh)
            pkList.Add(pkno)
        Next
        Return pkList
    End Function
    Public Function DeletePK(ByVal pkno As phukienno)
        Dim mapk As String = pkno.Mapk
        sqlstr = "DELETE PKNo WHERE MaPKNo='" & mapk & "'"
        Return connect.Excute(sqlstr)
    End Function
    Public Function InsertUpdatePK(ByVal pkno As phukienno, ByVal flag As Integer) As Boolean
        Try
            Dim mapk As String = pkno.Mapk
            Dim tenpk As String = pkno.Tenpk
            Dim giathanh As Single = pkno.Giathanh
            Dim dvtinh As String = pkno.Donvitinh
            If flag = 1 Then
                sqlstr = "INSERT PKNo(MaPKNo, TenPKNo, GiaThanh, DonViTinh) VALUES(N'" & mapk & "',N'" & tenpk & "', '" & giathanh & "', N'" & dvtinh & "')"
            End If
            If flag = 2 Then
                sqlstr = "Update PKNo SET TenPKNo=N'" & tenpk & "', GiaThanh='" & giathanh & "', DonViTinh=N'" & dvtinh & "' WHERE MaPKNo='" & mapk & "'"
            End If
            Return connect.Excute(sqlstr)
        Catch ex As Exception
            MsgBox("Dữ liệu phụ kiện nổ đã tồn tại, vui lòng kiểm tra lại", MsgBoxStyle.Exclamation, "Thông báo")
        End Try
        Return True
    End Function
#End Region

#Region "tabhochieukhoannomin"
    Public Function GetDataHC() As List(Of hcknm)
        Dim mahochieu, macongtruong, mamaykhoan, mamayno, mappn As String
        Dim tbatdau, tketthuc, ngaynomin, loaitn As String
        Dim ktm, kxl As String
        Dim docung As Integer
        Dim i As Integer
        Dim hochieu As hcknm
        Dim hcList As New List(Of hcknm)
        Dim dt As New DataTable
        dt = connect.GetData("Select * from HCKNM")
        For i = 0 To dt.Rows.Count - 1
            mahochieu = dt.Rows(i)("MaHoChieu")
            macongtruong = dt.Rows(i)("MaCongTruong")
            docung = dt.Rows(i)("DoCung")
            mamaykhoan = dt.Rows(i)("MaMayKhoan")
            mamayno = dt.Rows(i)("MaMayNoMin")
            mappn = dt.Rows(i)("MaPPNo")
            tbatdau = dt.Rows(i)("TBatdau")
            tketthuc = dt.Rows(i)("TKetthuc")
            ngaynomin = dt.Rows(i)("NgayNoMin")
            loaitn = dt.Rows(i)("LoaiTN")
            ktm = dt.Rows(i)("KTM")
            kxl = dt.Rows(i)("KXL")
            hochieu = New hcknm(mahochieu, macongtruong, docung, mamaykhoan, mamayno, mappn, tbatdau, tketthuc, ngaynomin, loaitn, ktm, kxl)
            hcList.Add(hochieu)
        Next
        Return hcList
    End Function
    Public Function DeleteHC(ByVal hochieu As hcknm)
        Dim mahochieu As String = hochieu.Mahochieu
        sqlstr = "DELETE HCKNM WHERE MaHoChieu='" & mahochieu & "'"
        Return connect.Excute(sqlstr)
    End Function
    Public Function InsertUpdateHC(ByVal hochieu As hcknm, ByVal flag As Integer) As Boolean
        Try
            Dim mahochieu As String = hochieu.Mahochieu
            Dim macongtruong As String = hochieu.Macongtruong
            Dim docung As Single = hochieu.Docung
            Dim mamaykhoan As String = hochieu.Mamaykhoan
            Dim mamayno As String = hochieu.Mamayno
            Dim mappn As String = hochieu.Maphuongphapno
            Dim tbatdau As String = hochieu.Thoigianbatdau
            Dim tketthuc As String = hochieu.Thoigianketthuc
            Dim ngaynomin As String = hochieu.Ngaynomin
            Dim loaitn As String = hochieu.Loaithuocno
            Dim ktm As String = hochieu.KTM
            Dim kxl As String = hochieu.KXL
            If flag = 1 Then
                sqlstr = "INSERT HCKNM(MaHoChieu, MaCongTruong,DoCung,MaMayKhoan,MaMayNoMin,MaPPNo,TBatDau,TKetThuc,NgayNoMin,LoaiTN,KTM,KXL) VALUES(N'" & mahochieu & "', '" & macongtruong & "', '" & docung & "','" & mamaykhoan & "', '" & mamayno & "', '" & mappn & "', '" & tbatdau & "','" & tketthuc & "','" & ngaynomin & "', N'" & loaitn & "',N'" & ktm & "',N'" & kxl & "')"
            End If
            If flag = 2 Then
                sqlstr = "UPDATE HCKNM SET MaCongTruong='" & macongtruong & "', DoCung='" & docung & "', MaMayKhoan='" & mamaykhoan & "',MaMayNoMin='" & mamayno & "',MaPPNo='" & mappn & "',TBatDau='" & tbatdau & "', TKetThuc='" & tketthuc & "',NgayNoMin='" & ngaynomin & "',LoaiTN=N'" & loaitn & "',KTM='" & ktm & "',KXL='" & kxl & "' WHERE MaHoChieu='" & mahochieu & "'"
            End If
            Return connect.Excute(sqlstr)
        Catch e As Exception
            MsgBox("Dữ liệu đã tồn tại hoặc không thể sửa đổi, vui lòng tạo mới hộ chiếu và xóa hộ chiếu muốn sửa đổi", MsgBoxStyle.Exclamation, "Thông báo")
        End Try
        Return True
    End Function
#End Region

#Region "tabhethong"
    Public Function Backup(ByVal filename As String, ByVal patch As String)
        Try
            Dim fullpatch As String = patch + "\" + filename + ".bak"
            sqlstr = " BACKUP DATABASE KHOANNOMIN_LOTHIEN TO DISK = '" & fullpatch & "' "
            Return connect.Excute(sqlstr)
        Catch e As Exception
            MsgBox("Sao lưu dữ liệu thất bại", MsgBoxStyle.Exclamation, "Thông báo")
        End Try
        Return True
    End Function
    Public Function Restore(ByVal dbname As String, ByVal patch As String) As Boolean
        Try
            sqlstr = " RESTORE DATABASE KHOANNOMIN_LOTHIEN FROM DISK='" & patch & "' "
            Return connect.Equals(sqlstr)
        Catch e As Exception
            MsgBox("Khôi phục dữ liệu thất bại", MsgBoxStyle.Exclamation, "Thông báo")
        End Try
        Return True
    End Function
#End Region

#Region "tabtinhtoan"
    Public Function TinhToan(ByVal mahochieu As String, ByVal ghichu As String)
        'Return connect.Calc(mahochieu)
        Try
        UpdateGhiChu(mahochieu, ghichu)
        Dim i As Integer
        Dim malokhoan As String
        Dim macongtruong As String
        Dim dklokhoan As Single
        Dim chieucaotang As Single
        Dim chieusau As Single
        Dim chitieutn As Single
        Dim matdonap As Single
        Dim duongcan As Single
        Dim kchang As Single
        Dim kccot As Single
        Dim luongthuocno As Single
        Dim khoiluongdatda As Single
        Dim chieudaicotthuoc As Single
        Dim chieudaibuamin As Single
        Dim chieudaibua As Single
            Dim ttlist As New List(Of ketquatinhtoan)
        Dim dt As New DataTable
        dt = connect.GetData("SELECT MaLoKhoan,LoKhoan.MaCongTruong,LoKhoan.DKLoKhoan,ChieuCaoTang,ChieuSau,ChiTieuTN,MatDoNap FROM (LoKhoan INNER JOIN HCKNM ON HCKNM.MaCongTruong=LoKhoan.MaCongTruong)inner join ThongSoQuyDinh on HCKNM.LoaiTN=ThongSoQuyDinh.LoaiTN WHERE HCKNM.MaHoChieu='" & mahochieu & "'")
        For i = 0 To dt.Rows.Count - 1
            malokhoan = dt.Rows(i)("MaLoKhoan")
            macongtruong = dt.Rows(i)("MaCongTruong")
            dklokhoan = dt.Rows(i)("DKLoKhoan")
            chieucaotang = dt.Rows(i)("ChieuCaoTang")
            chieusau = dt.Rows(i)("ChieuSau")
            chitieutn = dt.Rows(i)("ChiTieuTN")
            matdonap = dt.Rows(i)("MatDoNap")
            duongcan = 30 * (Convert.ToSingle(dklokhoan)) / 1000
            kchang = 0.86 * duongcan
            kccot = 0.86 * kchang
            khoiluongdatda = kchang * (Convert.ToSingle(chieucaotang)) * duongcan
            luongthuocno = (Convert.ToSingle(chitieutn)) * khoiluongdatda
            chieudaicotthuoc = luongthuocno / (Convert.ToSingle(matdonap))
            chieudaibuamin = 20 * (Convert.ToSingle(dklokhoan)) / 1000
            sqlstr = "Insert Tinhtoan(MaCongTruong,MaLoKhoan,W,a,b,Q,V,Lk,Lt,Lbmin,Lb) values('" & macongtruong & "','" & malokhoan & "', '" & duongcan & "','" & kchang & "','" & kccot & "','" & luongthuocno & "','" & khoiluongdatda & "','" & chieusau & "','" & chieudaicotthuoc & "','" & chieudaibuamin & "','" & chieudaibua & "')"
            connect.Excute(sqlstr)
            Next
        Catch ex As Exception
            MsgBox("Hộ chiếu này đã được tính toán, vui lòng chọn xem lại các hộ chiếu đã được tính toán!", MsgBoxStyle.Exclamation, "Thông báo")
        End Try
        Return True
    End Function
    Public Function UpdateGhiChu(ByVal mahochieu As String, ByVal ghichu As String)
        sqlstr = "Update HCKNM Set GhiChu=' " & ghichu & " ' where MaHoChieu='" & mahochieu & "'"
        Return connect.Excute(sqlstr)
    End Function
    Public Function GetDataTinhToan(ByVal mahochieu As String)
        Dim macongtruong, malokhoan As String
        Dim w, a, b, q, v, lk, lt, lbmin, lb As Single
        Dim i As Integer
        Dim tinhtoan As ketquatinhtoan
        Dim ttlist As New List(Of ketquatinhtoan)
        Dim dt As New DataTable
        dt = connect.GetData("select* from (Tinhtoan inner join LoKhoan on LoKhoan.MaLoKhoan=Tinhtoan.MaLoKhoan) inner join HCKNM on HCKNM.MaCongTruong=LoKhoan.MaCongTruong where HCKNM.MaHoChieu='" & mahochieu & "'")
        For i = 0 To dt.Rows.Count - 1
            macongtruong = dt.Rows(i)("MaCongTruong")
            malokhoan = dt.Rows(i)("MaLoKhoan")
            w = dt.Rows(i)("W")
            a = dt.Rows(i)("a")
            b = dt.Rows(i)("b")
            q = dt.Rows(i)("Q")
            v = dt.Rows(i)("V")
            lk = dt.Rows(i)("Lk")
            lt = dt.Rows(i)("Lt")
            lbmin = dt.Rows(i)("Lbmin")
            lb = dt.Rows(i)("Lb")
            tinhtoan = New ketquatinhtoan(macongtruong, malokhoan, w, a, b, q, v, lk, lt, lbmin, lb)
            ttlist.Add(tinhtoan)
        Next
        Return ttlist
    End Function
    Public Function GetDataDaTinhToan(ByVal mahochieu As String)
        Dim macongtruong, malokhoan As String
        Dim w, a, b, q, v, lk, lt, lbmin, lb As Single
        Dim i As Integer
        Dim tinhtoan As ketquatinhtoan
        Dim ttlist1 As New List(Of ketquatinhtoan)
        Dim dt As New DataTable
        dt = connect.GetData("select* from (Tinhtoan inner join LoKhoan on LoKhoan.MaLoKhoan=Tinhtoan.MaLoKhoan) inner join HCKNM on HCKNM.MaCongTruong=LoKhoan.MaCongTruong where HCKNM.MaHoChieu='" & mahochieu & "'")
        For i = 0 To dt.Rows.Count - 1
            macongtruong = dt.Rows(i)("MaCongTruong")
            malokhoan = dt.Rows(i)("MaLoKhoan")
            w = dt.Rows(i)("W")
            a = dt.Rows(i)("a")
            b = dt.Rows(i)("b")
            q = dt.Rows(i)("Q")
            v = dt.Rows(i)("V")
            lk = dt.Rows(i)("Lk")
            lt = dt.Rows(i)("Lt")
            lbmin = dt.Rows(i)("Lbmin")
            lb = dt.Rows(i)("Lb")
            tinhtoan = New ketquatinhtoan(macongtruong, malokhoan, w, a, b, q, v, lk, lt, lbmin, lb)
            ttlist1.Add(tinhtoan)
        Next
        Return ttlist1
    End Function
#End Region

    Public Function GetDataHCVe(ByVal mahochieu As String)
        Dim i As Integer
        Dim macongtruong As String = Nothing
        Dim h As Single
        Dim w As Single
        Dim a As Single
        Dim Lb As Single
        Dim Lt As Single
        Dim Lk As Single
        Dim Lkt As Single
        Dim dt As DataTable
        Dim thongsove As thongsovehinhchieubang
        Dim listvehcbang As New List(Of thongsovehinhchieubang)
        dt = connect.GetData("SELECT LoKhoan.MaCongTruong,ChieuCaoTang,ChieuSau,ChieuSaukt,W,a,Lb,Lt FROM (LoKhoan INNER JOin Tinhtoan ON Tinhtoan.MaLoKhoan=LoKhoan.MaLoKhoan)inner join HCKNM on HCKNM.MaCongTruong=Tinhtoan.MaCongTruong WHERE HCKNM.MaHoChieu='" & mahochieu & "'")
        For i = 0 To dt.Rows.Count - 1
            macongtruong = dt.Rows(i)("MaCongTruong")
            h = dt.Rows(i)("ChieuCaoTang")
            w = dt.Rows(i)("W")
            a = dt.Rows(i)("a")
            Lb = dt.Rows(i)("Lb")
            Lt = dt.Rows(i)("Lt")
            Lk = dt.Rows(i)("ChieuSau")
            Lkt = dt.Rows(i)("ChieuSaukt")
            thongsove = New thongsovehinhchieubang(macongtruong, h, w, a, Lb, Lt, Lk, Lkt)
            listvehcbang.Add(thongsove)
        Next
        Return listvehcbang
    End Function
    Public Function GetDataCongTruong(ByVal mahochieu As String)
        Dim macongtruong As String = Nothing
        Dim i As Integer
        Dim dt As DataTable
        dt = connect.GetData("SELECT LoKhoan.MaCongTruong FROM (LoKhoan INNER JOin Tinhtoan ON Tinhtoan.MaLoKhoan=LoKhoan.MaLoKhoan)inner join HCKNM on HCKNM.MaCongTruong=Tinhtoan.MaCongTruong WHERE HCKNM.MaHoChieu='" & mahochieu & "'")
        For i = 0 To dt.Rows.Count - 1
            macongtruong = dt.Rows(i)("MaCongTruong")
        Next
        Return macongtruong
    End Function
    Public Function GetDataLct(ByVal mahochieu As String)
        Dim Lct As Single
        Dim i As Integer
        Dim dt As DataTable
        dt = connect.GetData("SELECT LoKhoan.ChieuCaoTang FROM (LoKhoan INNER JOin Tinhtoan ON Tinhtoan.MaLoKhoan=LoKhoan.MaLoKhoan)inner join HCKNM on HCKNM.MaCongTruong=Tinhtoan.MaCongTruong WHERE HCKNM.MaHoChieu='" & mahochieu & "'")
        For i = 0 To dt.Rows.Count - 1
            Lct = dt.Rows(i)("ChieuCaoTang")
        Next
        Return Lct
    End Function
    Public Function GetDataw(ByVal mahochieu As String)
        Dim w As Single
        Dim i As Integer
        Dim dt As DataTable
        dt = connect.GetData("SELECT W FROM (LoKhoan INNER JOin Tinhtoan ON Tinhtoan.MaLoKhoan=LoKhoan.MaLoKhoan)inner join HCKNM on HCKNM.MaCongTruong=Tinhtoan.MaCongTruong WHERE HCKNM.MaHoChieu='" & mahochieu & "'")
        For i = 0 To dt.Rows.Count - 1
            w = dt.Rows(i)("W")
        Next
        Return w
    End Function
    Public Function GetDataa(ByVal mahochieu As String)
        Dim a As Single
        Dim i As Integer
        Dim dt As DataTable
        dt = connect.GetData("SELECT a FROM (LoKhoan INNER JOin Tinhtoan ON Tinhtoan.MaLoKhoan=LoKhoan.MaLoKhoan)inner join HCKNM on HCKNM.MaCongTruong=Tinhtoan.MaCongTruong WHERE HCKNM.MaHoChieu='" & mahochieu & "'")
        For i = 0 To dt.Rows.Count - 1
            a = dt.Rows(i)("a")
        Next
        Return a
    End Function
    Public Function GetDataLb(ByVal mahochieu As String)
        Dim Lb As Single
        Dim i As Integer
        Dim dt As DataTable
        dt = connect.GetData("SELECT Lb FROM (LoKhoan INNER JOin Tinhtoan ON Tinhtoan.MaLoKhoan=LoKhoan.MaLoKhoan)inner join HCKNM on HCKNM.MaCongTruong=Tinhtoan.MaCongTruong WHERE HCKNM.MaHoChieu='" & mahochieu & "'")
        For i = 0 To dt.Rows.Count - 1
            Lb = dt.Rows(i)("Lb")
        Next
        Return Lb
    End Function
    Public Function GetDataLt(ByVal mahochieu As String)
        Dim Lt As Single
        Dim i As Integer
        Dim dt As DataTable
        dt = connect.GetData("SELECT Lt FROM (LoKhoan INNER JOin Tinhtoan ON Tinhtoan.MaLoKhoan=LoKhoan.MaLoKhoan)inner join HCKNM on HCKNM.MaCongTruong=Tinhtoan.MaCongTruong WHERE HCKNM.MaHoChieu='" & mahochieu & "'")
        For i = 0 To dt.Rows.Count - 1
            Lt = dt.Rows(i)("Lt")
        Next
        Return Lt
    End Function
    Public Function GetDataLk(ByVal mahochieu As String)
        Dim Lt As Single
        Dim i As Integer
        Dim dt As DataTable
        dt = connect.GetData("SELECT ChieuSau FROM (LoKhoan INNER JOin Tinhtoan ON Tinhtoan.MaLoKhoan=LoKhoan.MaLoKhoan)inner join HCKNM on HCKNM.MaCongTruong=Tinhtoan.MaCongTruong WHERE HCKNM.MaHoChieu='" & mahochieu & "'")
        For i = 0 To dt.Rows.Count - 1
            Lt = dt.Rows(i)("ChieuSau")
        Next
        Return Lt
    End Function
    Public Function GetDataLkt(ByVal mahochieu As String)
        Dim Lkt As Single
        Dim i As Integer
        Dim dt As DataTable
        dt = connect.GetData("SELECT ChieuSaukt FROM (LoKhoan INNER JOin Tinhtoan ON Tinhtoan.MaLoKhoan=LoKhoan.MaLoKhoan)inner join HCKNM on HCKNM.MaCongTruong=Tinhtoan.MaCongTruong WHERE HCKNM.MaHoChieu='" & mahochieu & "'")
        For i = 0 To dt.Rows.Count - 1
            Lkt = dt.Rows(i)("ChieuSaukt")
        Next
        Return Lkt
    End Function
    Public Function GetDatab(ByVal mahochieu As String)
        Dim b As Single
        Dim i As Integer
        Dim dt As DataTable
        dt = connect.GetData("SELECT b FROM (LoKhoan INNER JOin Tinhtoan ON Tinhtoan.MaLoKhoan=LoKhoan.MaLoKhoan)inner join HCKNM on HCKNM.MaCongTruong=Tinhtoan.MaCongTruong WHERE HCKNM.MaHoChieu='" & mahochieu & "'")
        For i = 0 To dt.Rows.Count - 1
            b = dt.Rows(i)("b")
        Next
        Return b
    End Function
End Class
